import tkinter as tk
from tkinter import ttk, messagebox
import sqlite3

class UserManagementTab(tk.Frame):
    def __init__(self, master):
        super().__init__(master)
        self.master = master
        self.conn = sqlite3.connect('app.db')
        self.current_page = 1
        self.page_size = 10
        self.total_pages = 1
        self.create_table()
        self.create_widgets()

    def create_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE,
                role TEXT
            )
        ''')
        self.conn.commit()

    def create_widgets(self):
        # 查询条件区域
        search_frame = tk.Frame(self)
        search_frame.pack(pady=10, padx=10, fill=tk.X)

        tk.Label(search_frame, text="用户名:").pack(side=tk.LEFT)
        self.search_username = tk.Entry(search_frame, width=15)
        self.search_username.pack(side=tk.LEFT, padx=5)

        tk.Label(search_frame, text="角色:").pack(side=tk.LEFT, padx=10)
        self.search_role = tk.Entry(search_frame, width=15)
        self.search_role.pack(side=tk.LEFT)

        tk.Button(search_frame, text="查询", command=self.on_search).pack(side=tk.LEFT, padx=5)
        tk.Button(search_frame, text="添加用户", command=self.add_user).pack(side=tk.LEFT, padx=5)
        tk.Button(search_frame, text="删除用户", command=self.delete_user).pack(side=tk.LEFT)

        # 用户表格
        columns = ("ID", "用户名", "角色")
        self.tree = ttk.Treeview(self, columns=columns, show="headings", selectmode="browse")
        for col in columns:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=120)
        self.tree.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # 分页控件
        page_frame = tk.Frame(self)
        page_frame.pack(pady=5)

        self.prev_btn = tk.Button(page_frame, text="上一页", command=self.prev_page)
        self.prev_btn.pack(side=tk.LEFT, padx=5)

        self.page_label = tk.Label(page_frame, text="第1页/共1页")
        self.page_label.pack(side=tk.LEFT)

        self.next_btn = tk.Button(page_frame, text="下一页", command=self.next_page)
        self.next_btn.pack(side=tk.LEFT, padx=5)

        self.load_data()

    def on_search(self):
        self.current_page = 1
        self.load_data()

    def load_data(self):
        # 获取查询条件
        username = self.search_username.get().strip()
        role = self.search_role.get().strip()

        # 构建查询条件
        conditions = []
        params = []
        if username:
            conditions.append("username LIKE ?")
            params.append(f"%{username}%")
        if role:
            conditions.append("role = ?")
            params.append(role)

        where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""

        # 计算总记录数
        count_sql = f"SELECT COUNT(*) FROM users {where_clause}"
        cursor = self.conn.cursor()
        cursor.execute(count_sql, params)
        total = cursor.fetchone()[0]

        # 计算分页
        self.total_pages = max(1, (total + self.page_size - 1) // self.page_size)
        self.current_page = max(1, min(self.current_page, self.total_pages))
        offset = (self.current_page - 1) * self.page_size

        # 查询数据
        query = f"SELECT id, username, role FROM users {where_clause} LIMIT ? OFFSET ?"
        params.extend([self.page_size, offset])
        cursor.execute(query, params)
        rows = cursor.fetchall()

        # 更新界面
        self.tree.delete(*self.tree.get_children())
        for row in rows:
            self.tree.insert("", "end", values=row)

        # 更新分页状态
        self.page_label.config(text=f"第{self.current_page}页/共{self.total_pages}页")
        self.prev_btn["state"] = tk.NORMAL if self.current_page > 1 else tk.DISABLED
        self.next_btn["state"] = tk.NORMAL if self.current_page < self.total_pages else tk.DISABLED

    def prev_page(self):
        self.current_page -= 1
        self.load_data()

    def next_page(self):
        self.current_page += 1
        self.load_data()

    def add_user(self):
       
        add_window = tk.Toplevel(self)
        add_window.title("添加用户")
        add_window.transient(self.master)  # 设置为父窗口的子窗口
        add_window.grab_set()  # 设置# 

        # 获取父窗口的位置和大小
        parent_x = self.master.winfo_rootx()
        parent_y = self.master.winfo_rooty()
        parent_width = self.master.winfo_width()
        parent_height = self.master.winfo_height()

        # 设置弹出窗口的大小
        add_window_width = 300
        add_window_height = 150

        # 计算弹出窗口的位置
        add_window_x = parent_x + (parent_width - add_window_width) // 2
        add_window_y = parent_y + (parent_height - add_window_height) // 2

        # 设置弹出窗口的位置和大小
        add_window.geometry(f"{add_window_width}x{add_window_height}+{add_window_x}+{add_window_y}")


        tk.Label(add_window, text="用户名:").grid(row=0, column=0, padx=5, pady=5)
        username_entry = tk.Entry(add_window)
        username_entry.grid(row=0, column=1, padx=5, pady=5)

        tk.Label(add_window, text="角色:").grid(row=1, column=0, padx=5, pady=5)
        role_entry = tk.Entry(add_window)
        role_entry.grid(row=1, column=1, padx=5, pady=5)
       
        def save():
            username = username_entry.get().strip()
            role = role_entry.get().strip()
            if not username or not role:
                messagebox.showerror("错误", "用户名和角色不能为空")
                return

            try:
                cursor = self.conn.cursor()
                cursor.execute("INSERT INTO users (username, role) VALUES (?, ?)", 
                             (username, role))
                self.conn.commit()
                self.load_data()
                add_window.destroy()
            except sqlite3.IntegrityError:
                messagebox.showerror("错误", "用户名已存在")

        tk.Button(add_window, text="保存", command=save).grid(row=2, column=1, pady=10, sticky=tk.E)
     

    def delete_user(self):
        selected = self.tree.selection()
        if not selected:
            return

        user_id = self.tree.item(selected[0])["values"][0]
        if messagebox.askyesno("确认", "确定要删除该用户吗？"):
            cursor = self.conn.cursor()
            cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))
            self.conn.commit()
            self.load_data()

    def __del__(self):
        self.conn.close()

# 使用示例
if __name__ == "__main__":
    root = tk.Tk()
    root.title("用户管理系统")
    root.geometry("600x400")

    tab = UserManagementTab(root)
    tab.pack(fill=tk.BOTH, expand=True)

    root.mainloop()